from pandas.core.tools.datetimes import Scalar 

import requests 

import datetime 

import pandas as pd 

import pymysql 

from joblib import dump 

from sklearn.preprocessing import MinMaxScaler 

class WeatherUtils(object): 

    def __init__(self): 

        self.date = [ 

'2024-07-01', 

'2024-08-01', 

'2024-09-01', 

'2024-10-01', 

'2024-11-01', 

'2024-12-01', 

] 

        self.url = 'http://gfeljm.tianqiapi.com/api' 

def get_date(self): 

        """获取天气数据 

""" 

data_list = [] 

for d in self.date: 

    conf = { 

"appid": "13954192", 

"appsecret": "BA7zIjrm", 

"version": "history", 

"year": d[:4], 

"month": d[5:7], 

"city": "南昌" 

} 

res = requests.get(self.url+'?', params=conf) 

res_data = res.json() 

#print(res_data) 

for i in res_data['data']: 

    data_list.append({ 

'date': datetime.datetime.strptime(i['ymd'], '%Y-%m-%d'), 

'bWendu': i['bWendu'], 

'yWendu': i['yWendu'], 

'tianqi': i['tianqi'], 

'fengli': i['fengli'], 

}) 

df = pd.DataFrame(data_list) 

df.to_csv('./timing/weather.csv') 

return df 

    class MysqlUtils(object): 

        def __init__(self): 

            self.weather_data = [] 

    self.conn = pymysql.connect( 

host='127.0.0.1', 

user='root', 

password='sjk1234', 

db='scenic', 

port=3306, 

charset='utf8' 

) 

def is_holiday(self, date): 



    if str(date) in ['2024-09-03', '2024-10-01', '2024-10-02', '2024-10-03','2024-10-04', 

'2024-10-05', '2024-10-06', '2024-10-07', '2025-01-01', '2025-01-02', '2025-01-03', '2025-01-03']: 

        return 1 

    return 0 

def get_scenic_data(self): 


# 获取天气数据 

    wu = WeatherUtils() 

self.weather_data = wu.get_date() 

cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor) 

sql = """ 

SELECT DATE(g.create_time) as date, 

count(*) as count FROM order_user_gate_rel g 

WHERE DATE(g.create_time) < '2025-01-01' 

GROUP BY date 

""" 

cursor.execute(sql) 

ret = cursor.fetchall() 

df = pd.DataFrame(ret) 

df['date'] = pd.to_datetime(df['date']) 

df_pivot = pd.merge(self.weather_data, df, on='date') 

print(df_pivot) 

df_pivot.set_index('date', inplace=True) 

df_pivot['bWendu'] = df_pivot['bWendu'].str.replace('o', '').astype(int) 

df_pivot['yWendu'] = df_pivot['yWendu'].str.replace('o', '').astype(int) 

df_pivot['dow'] = df_pivot.index.dayofweek 

df_pivot['month'] = df_pivot.index.month 

#print(df_pivot) 

df_pivot['is_holiday'] = df_pivot.index.map(self.is_holiday) 

# 对星期几 

df_pivot = pd.get_dummies(df_pivot, columns=['dow', 'month', 'tianqi', 'fengli'], dtype=int) 

#归一化 

scaler = MinMaxScaler() 

features = df_pivot[['count']] 

df_pivot['count'] = scaler.fit_transform(features) 

#归一化天气 

weather_features = df_pivot[['bWendu', 'yWendu']] 

df_pivot[['bWendu', 'yWendu']] = scaler.fit_transform(weather_features) 

#print(df_pivot) 

df_pivot.to_csv('timing/scenic_data.csv', index=False) 

if __name__== '__main__': 

    mu = MysqlUtils() 

mu.get_scenic_data()